﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Reflection;
public enum eQQueryType
{
    Select = 1,
    Insert = 2,
    Update = 3,
    Delete = 4
}
public partial class QueryFacade : IDisposable
{
    public QueryFacade()
    {

    }

    #region props
    public eQQueryType QueryType { get; set; }
    public IList<string> GroupList { get; set; }
    public IList<QTableJoin> JoinList { get; set; }
    public IList<IQEvaluable> ColumnList { get; set; }
    public IList<IQEvaluable> ClauseList { get; set; }
    public IList<IQEvaluable> HavingClauseList { get; set; }
    public IList<QOrder> OrderList { get; set; }
    public IList<QSet> SetList { get; set; }
    public IList<InsertColumn> InsertColumnList { get; set; }
    public QTable Table { get; set; }
    public int _top { get; set; }

    #endregion
    public QueryFacade Top(int noOfRecords)
    {
        this._top = noOfRecords;
        return this;
    }
    #region Where Havnig

    public QueryFacade Where(IQEvaluable criteria)
    {
        //if (this.Clauses.Count == 0)
        //    this.Clauses.Add(new QClause(criteria));
        //else
        //    this.Clauses.Add(new QClause(eQLogicOperator.And, criteria));

        this.ClauseList.Add(criteria);

        return this;
    }

    public QueryFacade Where(IList<IQEvaluable> criterias)
    {
        this.ClauseList = criterias;
        return this;
    }
    public QueryFacade Where(params IQEvaluable[] criterias)
    {
        this.ClauseList = criterias.ToList<IQEvaluable>();
        return this;
    }

    public QueryFacade Having(IQEvaluable clause)
    {
        this.HavingClauseList.Add(clause);
        return this;
    }

    public QueryFacade Having(IList<IQEvaluable> clauses)
    {
        this.HavingClauseList = clauses;
        return this;
    }
    #endregion
    #region Join

    public QueryFacade Join<T>(string alias, IQEvaluable criteria)
    {
        return this.Join<T>(eQJoinType.InnerJoin, alias, (new List<IQEvaluable>() { criteria }));
    }
    public QueryFacade Join<T>(eQJoinType joinType, string alias, IQEvaluable criteria)
    {
        return this.Join<T>(joinType, alias, (new List<IQEvaluable>() { criteria }));
    }
    public QueryFacade Join<T>(string alias, IList<IQEvaluable> criterias)
    {
        return this.Join<T>(eQJoinType.InnerJoin, alias, criterias);
    }
    public QueryFacade Join<T>(string alias, string key, string keyRef)
    {
        return this.Join<T>(joinType: eQJoinType.InnerJoin, alias: alias, key: key, keyRef: keyRef);
    }
    public QueryFacade Join<T>(eQJoinType joinType, string alias, string key, string keyRef)
    {
        return this.Join<T>(joinType, alias, new List<IQEvaluable>() { new QCriteria(new QColumn(key), eQComparison.Eq, new QColumn(keyRef)) });
    }
    public QueryFacade Join<T>(eQJoinType joinType, string alias, IList<IQEvaluable> criterias)
    {
        QTableJoin tj = new QTableJoin();
        tj.JoinType = joinType;
        tj.Table.Type = typeof(T);
        tj.Table.Name = typeof(T).Name;
        tj.Table.Alias = alias;
        tj.Criterias = criterias;
        this.JoinList.Add(tj);
        return this;
    }
    #endregion
    #region OrderBy
    public QueryFacade OrderBy(QOrder order)
    {
        this.OrderList = new List<QOrder>() { order };
        return this;
    }

    public QueryFacade OrderBy(IList<QOrder> orders)
    {
        this.OrderList = orders;
        return this;
    }
    #endregion
    #region Select
    public QueryFacade Columns(List<Type> entityTypeList)
    {
        var findAlias = new Func<Type, string>((Type type) =>
        {
            if (this.Table.Type == type)
                return this.Table.Alias;
            foreach (QTableJoin tj in this.JoinList)
            {
                if (tj.Table.Type == type)
                    return tj.Table.Alias;
            }
            return null;
        });
        foreach (Type entityType in entityTypeList)
        {
            IList<PropertyInfo> properties = entityType.GetProperties().ToList();

            foreach (var property in properties)
            {
                string alias = findAlias.Invoke(entityType);
                if (string.IsNullOrEmpty(alias) == false)
                {
                    alias += ".";
                }
                this.ColumnList.Add(new QColumn(alias + property.Name));
            }
        }

        return this;
    }
    public QueryFacade Columns(Type entityType)
    {
        return this.Columns(new List<Type>() { entityType });
    }

    public QueryFacade Columns(params string[] columns)
    {
        foreach (string column in columns)
        {
            this.ColumnList.Add(new QColumn(column));
        }
        return this;
    }
    public QueryFacade Columns(params IQEvaluable[] columns)
    {
        foreach (IQEvaluable column in columns)
        {
            this.ColumnList.Add(column);
        }
        return this;
    }

    #endregion
    #region GroupBy
    public QueryFacade GroupBy(params string[] columns)
    {
        foreach (string column in columns)
        {
            this.GroupList.Add(column);
        }
        return this;
    }
    #endregion
    #region Set Statement (update query)

    public QueryFacade Set(IList<QSet> sets)
    {
        this.SetList = sets;
        return this;
    }
    public QueryFacade Set(QSet set)
    {
        this.SetList.Add(set);
        return this;
    }

    #endregion
    #region Select
    public QueryFacade Select<T>() where T : new()
    {
        return Select<T>(typeof(T).Name + "_");
    }
    public QueryFacade Select<T>(string alias)
    {
        return Query<T>(alias: alias, queryType: eQQueryType.Select);
    }
    #endregion
    #region Update
    public QueryFacade Update<T>()
    {
        return Query<T>(alias: typeof(T).Name + "_", queryType: eQQueryType.Update);
    }
    public QueryFacade Update<T>(string alias)
    {
        return Query<T>(alias: alias, queryType: eQQueryType.Update);
    }

    public QueryFacade Update<T>(T entity)
    {
        return Update<T>(entity, typeof(T) + "_");
    }

    public QueryFacade Update<T>(T entity, string alias)
    {
        QueryFacade qf = Query(alias: alias, queryType: eQQueryType.Update, type: entity.GetType());

        AdDbColumnInfoAttribute dbInfoAttr;
        System.Reflection.PropertyInfo pkProperty = null;
        AdDbColumnInfoAttribute pkDbInfoAttr = null;

        System.Reflection.PropertyInfo[] properties = entity.GetType().GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            dbInfoAttr = (AdDbColumnInfoAttribute)prop.GetCustomAttributes(true)[0];
            if (dbInfoAttr.IsPrimaryKey)
            {
                pkProperty = prop;
                pkDbInfoAttr = dbInfoAttr;
            }
            if (dbInfoAttr.IsAutoIncrement == false && dbInfoAttr.IsPrimaryKey == false)
            {
                var paramValue = prop.GetValue(entity, null);
                if (paramValue != null)
                {
                    qf.Set(new QSet(prop.Name, paramValue));
                }
            }
        }
        qf.Where(new QCriteria(pkProperty.Name, pkProperty.GetValue(entity, null)));
        return qf;
    }

    #endregion
    #region Insert
    public QueryFacade Insert<T>(params InsertColumn[] columns)
    {
        QueryFacade qf = Query<T>(alias: typeof(T).Name + "_", queryType: eQQueryType.Insert);
        qf.InsertColumnList = columns;
        return qf;
    }
    public QueryFacade Insert<T>(T entity)
    {
        IList<InsertColumn> insertColumnList = new List<InsertColumn>();

        AdDbColumnInfoAttribute dbInfoAttr;
        System.Reflection.PropertyInfo pkProperty = null;
        AdDbColumnInfoAttribute pkDbInfoAttr = null;

        System.Reflection.PropertyInfo[] properties = entity.GetType().GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            dbInfoAttr = (AdDbColumnInfoAttribute)prop.GetCustomAttributes(true)[0];
            if (dbInfoAttr.IsPrimaryKey)
            {
                pkProperty = prop;
                pkDbInfoAttr = dbInfoAttr;
            }
            if (dbInfoAttr.IsAutoIncrement == false && dbInfoAttr.IsPrimaryKey == false)
            {
                var paramValue = prop.GetValue(entity, null);
                if (paramValue != null)
                {
                    insertColumnList.Add(new InsertColumn(prop.Name, paramValue));
                }
            }
        }
        return Insert<T>(insertColumnList.ToArray());
    }
    #endregion
    #region Delete
    public QueryFacade Delete<T>()
    {
        return Query<T>(alias: typeof(T).Name + "_", queryType: eQQueryType.Delete);
    }
    public QueryFacade Delete<T>(T entity)
    {
        return Delete<T>(entity, typeof(T).Name + "_");
    }
    public QueryFacade Delete<T>(T entity, string alias)
    {
        QueryFacade qf = Query(alias: entity.GetType().Name + "_", queryType: eQQueryType.Delete, type: entity.GetType());

        AdDbColumnInfoAttribute dbInfoAttr;
        System.Reflection.PropertyInfo pkProperty = null;
        AdDbColumnInfoAttribute pkDbInfoAttr = null;

        System.Reflection.PropertyInfo[] properties = entity.GetType().GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            dbInfoAttr = (AdDbColumnInfoAttribute)prop.GetCustomAttributes(true)[0];
            if (dbInfoAttr.IsPrimaryKey)
            {
                pkProperty = prop;
                pkDbInfoAttr = dbInfoAttr;
            }
            //if (dbInfoAttr.IsAutoIncrement == false && dbInfoAttr.IsPrimaryKey == false)
            //{
            //    var paramValue = prop.GetValue(entity, null);
            //    if (paramValue != null)
            //    {
            //        qf.Set(new QSet(prop.Name, paramValue));
            //    }
            //}
        }
        qf.Where(new QCriteria(pkProperty.Name, pkProperty.GetValue(entity, null)));
        return qf;
    }
    public QueryFacade Delete<T>(string alias)
    {
        return Query<T>(alias: alias, queryType: eQQueryType.Delete);
    }
    #endregion
    #region Query
    public QueryFacade Query<T>(string alias, eQQueryType queryType)
    {
        return Query(alias: alias, queryType: queryType, type: typeof(T));
    }
    public QueryFacade Query(string alias, eQQueryType queryType, Type type)
    {
        // QueryFacade q = new QueryFacade();
        this.QueryType = queryType;

        this.JoinList = new List<QTableJoin>();
        this.ColumnList = new List<IQEvaluable>();
        this.ClauseList = new List<IQEvaluable>();
        this.HavingClauseList = new List<IQEvaluable>();
        this.GroupList = new List<string>();
        this.OrderList = new List<QOrder>();
        this.SetList = new List<QSet>();
        this.InsertColumnList = new List<InsertColumn>();
        this.Table = new QTable();
        this.Table.Type = type;
        this.Table.Name = type.Name;
        this.Table.Alias = alias;

        return this;
    }
    #endregion

    public override string ToString()
    {
        IQQueryBuilder queryBuilder = QDBFactory.GetQueryBuilderInstance();
        return queryBuilder.Build(this);

        //StringBuilder sb = new StringBuilder();

        //if (this.QueryType == eQQueryType.Select)
        //{
        //    sb.Append("select ");
        //    if (this._top > 0)
        //    {
        //        sb.AppendFormat(" top {0} ", this._top);
        //    }
        //    //select columns
        //    sb.AppendLine();
        //    if (this.ColumnList.Count == 0)//if select columns name are not specified
        //    {
        //        IList<PropertyInfo> properties;
        //        properties = this.Table.Type.GetProperties().ToList();

        //        for (int i = 0; i < properties.Count; i++)
        //        {
        //            var property = properties[i];
        //            IQEvaluable column = new QColumn(this.Table.Alias + "." + property.Name, property.Name);

        //            sb.AppendFormat("{0}", column.EvaluteWithAlias());

        //            if (i < properties.Count - 1)
        //                sb.Append(", ");
        //        }
        //        foreach (QTableJoin tj in this.JoinList)
        //        {
        //            properties = tj.Table.Type.GetProperties().ToList();
        //            sb.Append(", ");
        //            for (int i = 0; i < properties.Count; i++)
        //            {
        //                var property = properties[i];
        //                IQEvaluable column = new QColumn(tj.Table.Alias + "." + property.Name, property.Name);

        //                sb.AppendFormat("{0}", column.EvaluteWithAlias());

        //                if (i < properties.Count - 1)
        //                    sb.Append(", ");
        //            }
        //        }
        //    }
        //    else
        //    {
        //        for (int i = 0; i < this.ColumnList.Count; i++)
        //        {
        //            IQEvaluable column =(IQEvaluable ) this.ColumnList[i];

        //            sb.AppendFormat("{0}", column.EvaluteWithAlias());

        //            if (i < this.ColumnList.Count - 1)
        //                sb.Append(", ");
        //        }
        //    }
        //}
        //if (this.QueryType == eQQueryType.Update)
        //{
        //    sb.Append("update " + this.Table.Alias);
        //    sb.AppendLine();
        //    sb.Append("set ");
        //    for (int i = 0; i < this.SetList.Count; i++)
        //    {
        //        QSet set = this.SetList[i];
        //        sb.AppendFormat(set.Evalute());
        //        if (i < this.SetList.Count - 1)
        //        {
        //            sb.AppendLine(",");
        //        }

        //    }
        //}
        //if (this.QueryType == eQQueryType.Delete)
        //{
        //    sb.Append("delete " + this.Table.Alias);
        //}
        //if (this.QueryType == eQQueryType.Insert)
        //{
        //    sb.Append("insert into " + this.Table.Type.Name);

        //    StringBuilder sbColumns = new StringBuilder();
        //    StringBuilder sbValues = new StringBuilder();
        //    foreach (InsertColumn item in this.InsertColumnList)
        //    {
        //        sbColumns.Append(item.ColumnName);
        //        sbValues.Append(item.Value.Evalute());
        //        if (item != this.InsertColumnList.Last())
        //        {
        //            sbColumns.Append(", ");
        //            sbValues.Append(", ");
        //        }
        //    }
        //    sb.AppendFormat("({0})", sbColumns.ToString());
        //    sb.AppendFormat("values ({0})", sbValues.ToString());
        //}
        //if (this.QueryType != eQQueryType.Insert)
        //{
        //    //joins
        //    sb.AppendLine();
        //    sb.AppendFormat("from {0} {1}", this.Table.Name, this.Table.Alias);
        //    for (int i = 0; i < this.JoinList.Count; i++)
        //    {
        //        QTableJoin tj = this.JoinList[i];
        //        sb.AppendLine();
        //        // sb.AppendFormat("inner join {0} {1} on {2} = {3}", tj.TableName, tj.Alias, tj.Key, tj.KeyRef);
        //        if (tj.JoinType == eQJoinType.LeftJoin)
        //            sb.Append("left outer");
        //        else
        //            sb.Append("inner");
        //        sb.AppendFormat(" join {0} {1} on ", tj.Table.Name, tj.Table.Alias);
        //        for (int j = 0; j < tj.Criterias.Count; j++)
        //        {
        //            IQEvaluable criteria = tj.Criterias[j];
        //            sb.AppendFormat(" {0} ", criteria.Evalute());
        //            if (j < tj.Criterias.Count - 1)
        //                sb.Append(" and ");
        //        }
        //    }
        //    //where clause
        //    if (this.ClauseList.Count > 0)
        //    {
        //        sb.AppendLine();
        //        sb.AppendFormat("where ");
        //        for (int i = 0; i < this.ClauseList.Count; i++)
        //        {
        //            //IQCriteria clause = this.Clauses[i];              
        //            IQEvaluable clause = this.ClauseList[i];
        //            if (i > 0 && ((QCriteriaBase)clause).LogicalOp == null)
        //            {
        //                ((QCriteriaBase)clause).LogicalOp = eQLogicOperator.And;
        //            }
        //            sb.AppendLine();
        //            sb.Append(clause.Evalute());
        //        }
        //    }
        //    //group by 
        //    if (this.QueryType == eQQueryType.Select && this.GroupList.Count > 0)
        //    {
        //        sb.AppendLine();
        //        sb.AppendFormat("group by ");
        //        for (int i = 0; i < this.GroupList.Count; i++)
        //        {
        //            string group = this.GroupList[i];
        //            sb.Append(group);
        //            if (i < this.GroupList.Count - 1)
        //                sb.Append(", ");
        //        }
        //    }
        //    //having clause
        //    if (this.QueryType == eQQueryType.Select && this.HavingClauseList.Count > 0)
        //    {
        //        sb.AppendLine();
        //        sb.AppendFormat("having ");
        //        for (int i = 0; i < this.HavingClauseList.Count; i++)
        //        {
        //            IQEvaluable clause = this.HavingClauseList[i];
        //            sb.AppendLine();
        //            sb.Append(clause.Evalute());
        //        }
        //    }
        //    //order by 
        //    if (this.QueryType == eQQueryType.Select && this.OrderList.Count > 0)
        //    {
        //        sb.AppendLine();
        //        sb.Append("order by ");
        //        for (int i = 0; i < this.OrderList.Count; i++)
        //        {
        //            QOrder order = this.OrderList[i];
        //            sb.Append(order.Evalute());
        //            if (i < this.OrderList.Count - 1) sb.Append(", ");
        //        }
        //    }
        //}
        //System.Diagnostics.Debug.Print("--------------------------------------------");
        //System.Diagnostics.Debug.Print(sb.ToString());
        //return sb.ToString();
    }



}

